-- @owner: opentestcase009
-- @date: 2023/1/4
-- @testpoint: 层次递归查询函数验证
-- @modify: modified by opentestcase016 at 2023/05/24 代码变更，优化用例期望


--step1:创建表，插入数据;expect:建表成功，插入数据成功
drop table if exists t_connectby_024 cascade;
create table t_connectby_024(emp_id number(18),lead_id number(18),emp_name varchar2(200),salary number(10,2),dept_no varchar2(8));

insert into t_connectby_024 values('1',0,'king','1000000.00','001');
insert into t_connectby_024 values('2',1,'jack','50500.00','002');
insert into t_connectby_024 values('3',1,'arise','60000.00','003');
insert into t_connectby_024 values('4',2,'scott','30000.00','002');
insert into t_connectby_024 values('5',2,'tiger','25000.00','002');
insert into t_connectby_024 values('6',3,'wudde','23000.00','003');
insert into t_connectby_024 values('7',3,'joker','21000.00','003');

--step2:level伪列的使用，格式化层级;expect:查询成功
select lpad(' ',level*2,' ')||emp_name as name,emp_id,lead_id,salary,level from t_connectby_024 start with lead_id=0 connect by prior emp_id=lead_id;

--step3:connect_by_root查找根节点;expect:查询成功
select connect_by_root(emp_name) as r_name,emp_name,lead_id,salary from t_connectby_024 start with lead_id=1 connect by prior emp_id = lead_id;
select connect_by_root salary as r_salary,emp_name,lead_id,salary from t_connectby_024 start with lead_id=1 connect by prior emp_id = lead_id;

--step4:插入2条数据，与另2条emp_id=5/7的数据组成循环行;expect:插入成功
insert into t_connectby_024 values('2',5,'tiger_cycle','25000.00','002');
insert into t_connectby_024 values('3',7,'joker_cycle','21000.00','003');

--step5:connect_by_iscycle("cycle"), connect by nocycle;expect:循环行数据cycle值为1
select emp_id,emp_name,lead_id,salary,connect_by_iscycle as cycle from t_connectby_024 start with lead_id=0 connect by nocycle prior emp_id = lead_id;

--step6:connect_by_isleaf 查看是否是叶子节点;expect:叶子节点leaf值为1
select emp_id,emp_name,lead_id,salary,connect_by_isleaf as leaf from t_connectby_024 start with lead_id=0 connect by nocycle prior emp_id=lead_id;

--step7:sys_connect_by_path查看递归路径;expect:查看成功
select sys_connect_by_path(emp_name,'-') as path, *, level from t_connectby_024 start with lead_id=0 connect by nocycle prior emp_id=lead_id;

--step8:清理环境;expect:成功
drop table t_connectby_024 cascade;
